﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;

namespace OALPTool
{
    public partial class Form1 : Form
    {
        private int ret;
        private string strMsg;
        private DataView dvExcel = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void btnSelFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Title = "请选择Excel文件";
            file.Filter = "Excel文件(*.xls)|*.xls";
            if (file.ShowDialog() == DialogResult.OK)
            {
                this.tbFilePath.Text = file.FileName;
                try
                {
                    ArrayList sheets = ExcelSheetName(file.FileName.Trim());
                    this.cmbSheet.Items.Clear();
                    for (int i = 0; i < sheets.Count; i++)
                    {
                        this.cmbSheet.Items.Add(sheets[i].ToString().Trim());
                    }
                    if (this.cmbSheet.Items.Count > 0)
                    {
                        this.cmbSheet.SelectedIndex = 0;
                    }
                }
                catch (Exception ex)
                {
                    TSysPublic.ShowErrMsg(ex.Message);
                }
            }
            else
            {
                return;
            }
        }

        private void btnInput_Click(object sender, EventArgs e)
        {
            if (dvExcel != null)
            {
                dvExcel = null;
                dgvTmplate.DataSource = null;
            }
            if (this.tbFilePath.Text == "" || this.cmbSheet.Text == "")
            {
                TSysPublic.ShowMsg("请输入需要导入excel的路径和sheet表名！");
                return;
            }
            //读取Excel数据
            string sExcelFile = this.tbFilePath.Text.Trim();
            string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + sExcelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            OleDbConnection connection = new OleDbConnection(sConnectionString);
            string strLinkSql = "Select * from [" + this.cmbSheet.Text.Trim() + "$]";

            OleDbDataAdapter adp = new OleDbDataAdapter(strLinkSql, connection);

            DataSet ds = new DataSet();
            try
            {
                adp.Fill(ds, "Book1");
            }
            catch (Exception ex)
            {
                TSysPublic.ShowMsg("读取表底示数数据不成功：" + ex.Message);

                return;
            }
            if (ds == null || ds.Tables.Count < 0 || ds.Tables[0].Rows.Count < 0)
            {
                TSysPublic.ShowMsg("读取表底示数数据为空！");

                return;
            }

            dvExcel = new DataView(ds.Tables[0]);

            //设置Excel格式
            //ret = FormatExcelData();
            //if (ret < 0)
            //{
            //    TSysPublic.ShowMsg("设置格式失败！");
            //    return;
            //}

            //dgvTmplate.AutoGenerateColumns = false;
            dgvTmplate.DataSource = dvExcel;
        }


        /// <summary>
        /// Excel连接串
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <returns></returns>
        internal string Con2Excel(string strFilePath)
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=\"Excel 8.0;\"";
        }

        /// <summary>
        /// 获得Excel中的所有sheetname
        /// </summary>
        /// <param name="strFileName"></param>
        /// <returns></returns>
        internal ArrayList ExcelSheetName(string strFilePath)
        {
            ArrayList al = new ArrayList();
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(Con2Excel(strFilePath));
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable
                (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2].ToString().Replace("$", "").Replace("'", ""));
            }
            return al;
        }

    }
}
